package com.lams.jdbc;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.springframework.util.Assert;

import com.lams.util.SqlBean;
import com.lams.util.XmlSqlCodeFactroy;
import com.opensymphony.xwork2.ActionContext;


public class QueryUtils {
	private static Logger logger =Logger.getLogger(QueryUtils.class);
//	private static String CONNECTION_NAME = "lams";
//	private static Connection conn;
	private static IBaseDAO baseDao;
	
	public void setBaseDao(IBaseDAO dao){
		QueryUtils.baseDao = dao;
	}
	/**
	 * 通用查询，返回结果类型 List< Map<列名,值>>
	 * @param sql 查询SQL
	 * @return List
	 */
	public static List executeQuery(String sql){
		return executeQuery(0, Integer.MAX_VALUE, sql);
	}

	/**
	 * 根据SQLCODE取得SQL语句，替换参数
	 * @param sqlCode SQL编码
	 * @param params 参数列表 
	 * @return sql语句
	 */
	public static String getSqlBySqlCode(String sqlCode,Map params){
		try {
			return getSqlBySqlCode(sqlCode, params, ActionContext.getContext().getSession());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据编码查询SQL
	 * @param sqlCode Sql编码
	 * @return SqlBean
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static SqlBean getSqlBySqlCode(String sqlCode){
		Assert.hasText(sqlCode);
		return XmlSqlCodeFactroy.getSqlBeanByCode(sqlCode);
	}
	/**
	 * 根据编码查询SQL并替换参数
	 * @param sqlCode sql编码
	 * @param params 参数列表 Map
	 * @param sessions session
	 * @return String
	 * @throws SQLException 
	 */
	public static String getSqlBySqlCode(String sqlCode,Map params, Map sessions) throws SQLException{
		Assert.hasText(sqlCode);
		SqlBean sqlbean = getSqlBySqlCode(sqlCode);
		if(sqlbean == null){
			throw new SQLException("未找到SQL_CODE为["+sqlCode+"]的SQL");
		}
		if(sqlbean.getFields() == null || sqlbean.getFields().length == 0 || params==null || params.size() == 0)
			return sqlbean.getContent();
		String strSql = sqlbean.getContent();
		String[] fields = sqlbean.getFields();
		String strValue,strField;
		for(String field:fields){
			String tmp = field.trim();
			strField = "@@" + tmp + ",";
			if(params.containsKey(tmp)){
				Object obj = params.get(tmp);
				Class type = obj.getClass();
				if(type.isArray())
					strValue = ((String[])obj)[0];
				else if(obj instanceof String)
					strValue = String.valueOf(obj);
				else
					strValue = String.valueOf(obj);
			}else if(sessions.containsKey(tmp)){
				strValue = String.valueOf(sessions.get(tmp));
				
			}else{
				throw new SQLException("未找到名为["+tmp+"]的参数");
			}
			if(strSql.indexOf(strField)>0)
				strSql = strSql.replaceAll(strField,strValue);
		}
		if(logger.isDebugEnabled()){
			logger.debug("替换后SQL:["+strSql+"]");
		}
		
		return strSql;
	}
	/**
	 * 单条记录查询
	 * @param sql
	 * @return
	 */
	public static Map executeRecord(String sql){
		try {
			return baseDao.executeQueryRecode(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询记录条数
	 * @param sql
	 * @return
	 */
	public static int getTotalCount(String sql){
		String strSql = "select count(1) TOTALCOUNT FROM ("+removeOrder(sql)+")T";//"select count(1) TOTALCOUNT FROM (" + sql +") t";
		Map map = executeRecord(strSql);
		return Integer.parseInt(String.valueOf(map.get("TOTALCOUNT")));
	}

	/**
	 * 分页查询
	 * @param start 开始记录数
	 * @param limit 查询记录数
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List executeQuery(int start,int limit,String sql){
		Assert.hasText(sql);
		String strSql = sql+ " LIMIT "+ start + "," + limit;
		List result = new ArrayList();
		try {
			result = baseDao.executeQuery(strSql);
//			baseDao.freeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	private static String removeOrder(String sql){
		Assert.hasText(sql);
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(sql);
		StringBuffer sb = new StringBuffer();
		while(m.find()){
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}
	private static String removeSelect(String sql){
		Assert.hasText(sql);
		int beginPos = sql.toLowerCase().indexOf("from");
		Assert.isTrue(beginPos != -1, " sql : " + sql + " must has a keyword 'from'");
		return sql.substring(beginPos);
				
	}
//	public static void main(String[] args) {
//		Map map = new HashMap();
//		map.put("aaa", "aaa");
//		String key = "aaa";
//		System.out.println(map.containsKey(key));
//	}
}
